/*
 * Debts Manager. Simple android application for managing debts.
 * Copyright (C) 2010 Maxim Manuylov
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl-2.0.html>.
 */

package manuylov.maxim.dm.data;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import manuylov.maxim.common.database.SQLResultProcessor;
import manuylov.maxim.common.database.SQLRunner;
import manuylov.maxim.dm.Constants;
import manuylov.maxim.dm.data.object.*;

import java.util.*;

public class DataManager {
    private final SQLRunner mySQLRunner;

    private final static SQLResultProcessor<List<Person>> PERSON_PROCESSOR = new SQLResultProcessor<List<Person>>() {
        public List<Person> process(final Cursor cursor) {
            final List<Person> persons = new ArrayList<Person>(cursor.getCount());
            while (cursor.moveToNext()) {
                persons.add(new Person(cursor.getInt(0), cursor.getString(1)));
            }
            return persons;
        }
    };

    private final static SQLResultProcessor<List<Subject>> SUBJECT_PROCESSOR = new SQLResultProcessor<List<Subject>>() {
        public List<Subject> process(final Cursor cursor) {
            final List<Subject> subjects = new ArrayList<Subject>(cursor.getCount());
            while (cursor.moveToNext()) {
                subjects.add(new Subject(cursor.getInt(0), cursor.getString(1)));
            }
            return subjects;
        }
    };

    private final static SQLResultProcessor<List<String>> STRING_LIST_PROCESSOR = new SQLResultProcessor<List<String>>() {
        public List<String> process(final Cursor cursor) {
            final List<String> subjects = new ArrayList<String>(cursor.getCount());
            while (cursor.moveToNext()) {
                subjects.add(cursor.getString(0));
            }
            return subjects;
        }
    };

    private final static SQLResultProcessor<List<SubjectEx>> SUBJECT_EX_PROCESSOR = new SQLResultProcessor<List<SubjectEx>>() {
        public List<SubjectEx> process(final Cursor cursor) {
            final List<SubjectEx> subjects = new ArrayList<SubjectEx>(cursor.getCount());
            while (cursor.moveToNext()) {
                subjects.add(new SubjectEx(cursor.getInt(0), cursor.getString(1), cursor.getInt(2)));
            }
            return subjects;
        }
    };

    private final static SQLResultProcessor<List<DebtsListItem>> DEBT_PROCESSOR = new SQLResultProcessor<List<DebtsListItem>>() {
        private final Comparator<Debt> DEBTS_COMPARATOR_BY_DATE = new Comparator<Debt>() {
            public int compare(final Debt debt1, final Debt debt2) {
                return debt2.getId() - debt1.getId();
            }
        };

        private final Comparator<SortedSet<Debt>> DEBT_GROUPS_COMPARATOR_BY_DATE = new Comparator<SortedSet<Debt>>() {
            public int compare(final SortedSet<Debt> set1, final SortedSet<Debt> set2) {
                return DEBTS_COMPARATOR_BY_DATE.compare(set1.first(), set2.first());
            }
        };

        public List<DebtsListItem> process(final Cursor cursor) {
            if (!cursor.moveToNext()) {
                return Collections.emptyList();
            }

            final Map<Integer, SortedSet<Debt>> debtsGroupedBySubjectId = new HashMap<Integer, SortedSet<Debt>>();
            int debtCount = 0;
            do {
                final Person person = new Person(cursor.getInt(1), cursor.getString(2));
                final int subjectId = cursor.getInt(3);
                final Subject subject = new Subject(subjectId, cursor.getString(4));
                final int recordType = cursor.getInt(6);
                final Date date = new Date(cursor.getLong(7) * 1000);
                final Debt debt = new Debt(cursor.getInt(0), person, subject, cursor.getInt(5), recordType, date, cursor.getString(8));
                readPartialPayments(cursor, debt);
                getOrCreateDebtsSet(debtsGroupedBySubjectId, subjectId).add(debt);
                debtCount++;
            } while (!cursor.isAfterLast());

            final List<SortedSet<Debt>> debtGroups = new ArrayList<SortedSet<Debt>>(debtsGroupedBySubjectId.values());
            Collections.sort(debtGroups, DEBT_GROUPS_COMPARATOR_BY_DATE);

            final List<DebtsListItem> debts = new ArrayList<DebtsListItem>(debtCount + debtGroups.size());

            for (final SortedSet<Debt> debtGroup : debtGroups) {
                debts.addAll(debtGroup);
                debts.add(new DebtsSummary(debtGroup.first().getSubject(), debtGroup));
            }

            return debts;
        }

        private SortedSet<Debt> getOrCreateDebtsSet(final Map<Integer, SortedSet<Debt>> map, final int subjectId) {
            if (!map.containsKey(subjectId)) {
                map.put(subjectId, new TreeSet<Debt>(DEBTS_COMPARATOR_BY_DATE));
            }
            return map.get(subjectId);
        }

        private void readPartialPayments(final Cursor cursor, final Debt debt) {
            if (cursor.isNull(9)) {
                cursor.moveToNext();
                return;
            }
            final int debtId = debt.getId();
            do {
                final int partialPaymentId = cursor.getInt(9);
                final int quantity = cursor.getInt(10);
                final Date date = new Date(cursor.getLong(11) * 1000);
                final PartialPayment partialPayment = new PartialPayment(partialPaymentId, debtId, quantity, date);
                debt.addPartialPayment(partialPayment);
            } while (cursor.moveToNext() && cursor.getInt(0) == debtId);
        }
    };

    private final static Comparator<String> STRING_COMPARATOR = new Comparator<String>() {
        public int compare(final String object1, final String object2) {
            return object1.compareToIgnoreCase(object2);
        }
    };

    private final static Comparator<SimpleObject> SIMPLE_OBJECTS_COMPARATOR_BY_NAME = new Comparator<SimpleObject>() {
        public int compare(final SimpleObject object1, final SimpleObject object2) {
            return STRING_COMPARATOR.compare(object1.getName(), object2.getName());
        }
    };

    public DataManager(final Context context) {
        mySQLRunner = new SQLRunner(new DatabaseHelper(context));
    }

    public void addDebt(final int recordType,
                        final String personName,
                        final String subjectName,
                        final int quantity,
                        final String comment) {
        final int personId = getOrCreatePersonId(personName);
        final int subjectId = getOrCreateSubjectId(subjectName);
        mySQLRunner.executeWrite("INSERT INTO debt(personId, subjectId, quantity, type, date, comment) VALUES(?, ?, ?, ?, strftime('%s', 'now'), ?)", personId, subjectId, quantity, recordType, comment);
    }

    public void addPartialPayment(final int debtId, final int quantity) {
        mySQLRunner.executeWrite("INSERT INTO partial_payment(debtId, quantity, date) VALUES(?, ?, strftime('%s', 'now'))", debtId, quantity);
    }

    private String getDebtTableName(final int balanceMode) {
        return balanceMode == Constants.BALANCE_MODE_ACTUAL ? "debt" : "debt_history";
    }

    public List<Person> getActivePersons(final int balanceMode) {
        return doGetPersons(true, balanceMode);
    }

    private List<Person> doGetPersons(final boolean active, final int balanceMode) {
        final StringBuilder sql = new StringBuilder("SELECT id, name FROM person ");
        if (active) {
            sql.append("WHERE EXISTS(SELECT * FROM %dtn% WHERE %dtn%.personId = person.id)");
            insertDebtTableName(sql, balanceMode);
        } else {
            sql.append("WHERE NOT EXISTS(SELECT * FROM debt WHERE debt.personId = person.id ");
            sql.append("UNION SELECT * FROM debt_history WHERE debt_history.personId = person.id)");
        }
        final List<Person> persons = mySQLRunner.executeRead(sql.toString(), PERSON_PROCESSOR);
        sortSimpleObjects(persons);
        return persons;
    }

    public List<Person> getInactivePersons() {
        return doGetPersons(false, 0);
    }

    public List<String> getAllPersons() {
        final StringBuilder sql = new StringBuilder("SELECT name FROM person");
        final List<String> persons = mySQLRunner.executeRead(sql.toString(), STRING_LIST_PROCESSOR);
        sortStrings(persons);
        return persons;
    }

    public void removePersons(final List<Person> persons, final boolean[] checked) {
        doRemoveSimpleObjects("person", persons, checked);
    }

    public void removeSubjects(final List<Subject> subject, final boolean[] checked) {
        doRemoveSimpleObjects("subject", subject, checked);
    }

    private void doRemoveSimpleObjects(final String tableName, final List<? extends SimpleObject> objects, final boolean[] checked) {
        final StringBuilder sql = new StringBuilder("DELETE FROM ");
        sql.append(tableName);
        sql.append(" WHERE id IN (");
        final int length = checked.length;
        assert objects.size() == length;
        boolean first = true;
        for (int i = 0; i < length; i++) {
            if (checked[i]) {
                if (!first) {
                    sql.append(", ");
                }
                sql.append(objects.get(i).getId());
                first = false;
            }
        }
        assert !first;
        sql.append(")");
        mySQLRunner.executeWrite(sql.toString());
    }

    private void insertDebtTableName(final StringBuilder sql, final int balanceMode) {
        final String debtTableName = getDebtTableName(balanceMode);
        final String debtTableNamePattern = "%dtn%";
        final int debtTableNamePatternLength = debtTableNamePattern.length();
        int pos = sql.indexOf(debtTableNamePattern);
        while (pos >= 0) {
            sql.replace(pos, pos + debtTableNamePatternLength, debtTableName);
            pos = sql.indexOf(debtTableNamePattern);
        }
    }

    private void sortSimpleObjects(final List<? extends SimpleObject> list) {
        Collections.sort(list, SIMPLE_OBJECTS_COMPARATOR_BY_NAME);
    }

    private void sortStrings(final List<String> list) {
        Collections.sort(list, STRING_COMPARATOR);
    }

    public List<String> getAllSubjects() {
        final StringBuilder sql = new StringBuilder("SELECT name FROM subject");
        final List<String> subjects = mySQLRunner.executeRead(sql.toString(), STRING_LIST_PROCESSOR);
        sortStrings(subjects);
        return subjects;
    }

    public List<Subject> getInactiveSubjects() {
        final StringBuilder sql = new StringBuilder("SELECT id, name FROM subject ");
        sql.append("WHERE NOT EXISTS(SELECT * FROM debt WHERE debt.subjectId = subject.id ");
        sql.append("UNION SELECT * FROM debt_history WHERE debt_history.subjectId = subject.id)");
        final List<Subject> subjects = mySQLRunner.executeRead(sql.toString(), SUBJECT_PROCESSOR);
        sortSimpleObjects(subjects);
        return subjects;
    }

    public List<SubjectEx> getActiveSubjectsWithBalance(final int balanceMode) {
        final StringBuilder sql = new StringBuilder("");
        final String remainder;
        if (balanceMode == Constants.BALANCE_MODE_ACTUAL) {
            remainder = "(debt.quantity - (SELECT TOTAL(quantity) FROM partial_payment WHERE debtId = debt.id))";
        } else {
            remainder = "debt_history.quantity";
        }
        sql.append("SELECT subject.id, subject.name, SUM(");
        sql.append(remainder);
        sql.append(" * (2 * %dtn%.type - 1)) ");
        sql.append("FROM subject INNER JOIN %dtn% ON %dtn%.subjectId = subject.id ");
        sql.append("GROUP BY subject.id, subject.name ");
        insertDebtTableName(sql, balanceMode);
        final List<SubjectEx> subjects = mySQLRunner.executeRead(sql.toString(), SUBJECT_EX_PROCESSOR);
        sortSimpleObjects(subjects);
        return subjects;
    }

    public List<DebtsListItem> getDebtsByPerson(final int personId, final int balanceMode) {
        final StringBuilder sql = new StringBuilder("");
        sql.append("SELECT %dtn%.id, %dtn%.personId, person.name, %dtn%.subjectId, subject.name, %dtn%.quantity, %dtn%.type, %dtn%.date, %dtn%.comment, partial_payment.id, partial_payment.quantity, partial_payment.date ");
        sql.append("FROM %dtn% ");
        sql.append("INNER JOIN person ON %dtn%.personId = person.id ");
        sql.append("INNER JOIN subject ON %dtn%.subjectId = subject.id ");
        sql.append("LEFT OUTER JOIN partial_payment ON %dtn%.id = partial_payment.debtId ");
        sql.append("WHERE %dtn%.personId = ");
        sql.append(personId);
        sql.append(" ORDER BY %dtn%.id DESC, partial_payment.id ASC");
        insertDebtTableName(sql, balanceMode);
        return mySQLRunner.executeRead(sql.toString(), DEBT_PROCESSOR);
    }

    public void removeDebt(final Debt debt, final int balanceMode) {
        final int debtId = debt.getId();
        if (balanceMode == Constants.BALANCE_MODE_ACTUAL) {
            addPartialPayment(debtId, debt.getRemainder());
            final StringBuilder sql = new StringBuilder("");
            sql.append("INSERT INTO debt_history(id, personId, subjectId, quantity, type, date, comment) ");
            sql.append("SELECT id, personId, subjectId, quantity, type, date, comment FROM debt WHERE id = ?");
            mySQLRunner.executeWrite(sql.toString(), debtId);
            mySQLRunner.executeWrite("DELETE FROM debt WHERE id = ?", debtId);
        } else {
            mySQLRunner.executeWrite("DELETE FROM partial_payment WHERE debtId = ?", debtId);
            mySQLRunner.executeWrite("DELETE FROM debt_history WHERE id = ?", debtId);
        }
    }

    public void updateComment(final int debtId, final String newComment, final int balanceMode) {
        final StringBuilder sql = new StringBuilder("UPDATE %dtn% SET comment = ? WHERE id = ?");
        insertDebtTableName(sql, balanceMode);
        mySQLRunner.executeWrite(sql.toString(), newComment, debtId);
    }

    private Integer getOrCreatePersonId(final String personName) {
        Integer personId = mySQLRunner.getInt("SELECT id FROM person WHERE name = ?", personName);
        if (personId == null) {
            personId = (int) mySQLRunner.executeInsert("INSERT INTO person(name) VALUES(?)", personName);
        }
        return personId;
    }

    private Integer getOrCreateSubjectId(final String subjectName) {
        Integer subjectId = mySQLRunner.getInt("SELECT id FROM subject WHERE name = ?", subjectName);
        if (subjectId == null) {
            subjectId = (int) mySQLRunner.executeInsert("INSERT INTO subject(name) VALUES(?)", subjectName);
        }
        return subjectId;
    }

    public void close() {
        mySQLRunner.close();
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "debts_manager.db";
        private static final int DATABASE_VERSION = 3;

        public DatabaseHelper(final Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(final SQLiteDatabase db) {
            db.execSQL("CREATE TABLE person (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "name TEXT);");
            db.execSQL("CREATE TABLE subject (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "name TEXT);");
            createDebtTable(db, "debt", true);
            createPartialPaymentTable(db);
            createDebtTable(db, "debt_history", false);
        }

        private void createDebtTable(final SQLiteDatabase db, final String tableName, final boolean autoIncrement) {
            db.execSQL("CREATE TABLE " + tableName + " (" +
                    "id INTEGER PRIMARY KEY" +
                    (autoIncrement ? " AUTOINCREMENT, " : ", ") +
                    "personId INTEGER, " +
                    "subjectId INTEGER, " +
                    "quantity INTEGER, " +
                    "type INTEGER, " +
                    "date TIMESTAMP, " +
                    "comment TEXT);");
        }

        private void createPartialPaymentTable(final SQLiteDatabase db) {
            db.execSQL("CREATE TABLE partial_payment (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "debtId INTEGER, " +
                    "quantity INTEGER, " +
                    "date TIMESTAMP);");
        }

        @Override
        public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
            switch (oldVersion) {
                case 1: {
                    createPartialPaymentTable(db);
                }
                case 2: {
                    createDebtTable(db, "debt_history", false);
                }
            }
        }
    }
}
